#!/usr/bin/perl 
#
#   :..::.:..:
#   ::
#   ..   mspacman.pl
#   ..   dbsr@moscownights.nl
#   .    13:57:43 - 20-09-2012
#   ::
#   .::: .::: :::. :: :..

use DBI;
use Term::ANSIColor;

use strict; use warnings;


# # #
# VARIABLES #
# # #

# user vars
my $db = "$ENV{'HOME'}/.mspacman/mspacman.db";

# SQLite vars
my $packages = "packages";

# static 2d array
my @static_array;
# dynamic 2d array that will act as a temporary container for expac/cower data
my @pkg_array;

# constants
my $NAME            = 0;
my $VERSION         = 1;
my $LICENSE         = 2;
my $PACKAGE_GROUP   = 3;
my $DESCRIPTION     = 4;
my $DOWNLOAD_SIZE   = 5;
my $INSTALL_SIZ     = 6;
my $BUILD_DATE      = 7;
my $DEPENDS_ON      = 8;
my $DEPENDS_ON_P    = 9;
my $OPTIONAL_DEPS   = 10;
my $OPTIONAL_DEPS_P = 11;
my $REQUIRED_BY     = 12;
my $PACKAGER        = 13;
my $PROJECT_URL     = 14;

my $SQLITE          = 0;
my $SQLITES         = 1;
my $EXPAC           = 2;
my $EXPACS          = 3;
my $INDEX           = 15;


@static_array = ( [ "name","version","license","packageGroup","description","downloadSize","installSize","buildDate","dependsOn","dependsOnP","optionalDeps","optionalDepsP","requiredBy","packager","projectUrl" ],              
                  [ "name\,","version\,","license\,","packageGroup\,","description\,","downloadSize\,","installSize\,","buildDate\,","dependsOn\,","dependsOnP\,","optionalDeps\,","optionalDepsP\,","requiredBy\,","packager\,","projectUrl" ],              
                  [ "%n ", "%v ",    "%L ",    "%G ",         "%d ",        "%k ",         "%m ",        "%b ",      "%D ",      "%E ",       "%O ",         "%o ",          "%N ",       "%p ",     "%u " ],
                  [ "%n²", "%v²",    "%L²",    "%G²",         "%d²",       "%k²",         "%m²",        "%b²",      "%D²",      "%E²",       "%O²",         "%o²",          "%N²",       "%p²",     "%u" ]
              );




# # #
# FUNCTIONS # 
# # #


sub newDatabase 
# {{{
{
    print "Creating new pkg database in default location: '$db'\n";
    if (-e $db)
    {
        print "Removing existing database...\n";
        unlink($db);
    }
    my $dbh = DBI->connect( "dbi:SQLite:$db" ) || die "Cannot connect: $DBI::errstr";
    print "Creating database structure..\n";
    $dbh->do( "CREATE TABLE $packages (id            INTEGER PRIMARY KEY,
                                      name           TEXT,
                                      version        TEXT,
                                      license        TEXT,
                                      packageGroup   TEXT,
                                      description    TEXT,
                                      downloadSize   INTEGER,
                                      installSize    INTEGER, 
                                      buildDate      TEXT,
                                      dependsOn      TEXT,
                                      dependsOnP     TEXT,
                                      optionalDeps   TEXT,
                                      optionalDepsP  TEXT, 
                                      requiredBy     TEXT,
                                      packager       TEXT,
                                      projectUrl     TEXT)")

    || die "Cannot create table '\$$packages': $DBI::errstr";
    
    # full sync expac
    print "Starting full expac sync...\n";
    @pkg_array = queryExpac(  "\"@{ $static_array[$EXPACS] }\" -S" );
    print "Expac sync succesfull, starting SQLite transaction...\n";    
    # prepare sql transactions
    DBI->trace( 1, 'logfile.txt');
    my $sql = "INSERT INTO $packages ( @{$static_array[$SQLITES]} ) VALUES ( ?,?,?,?,?,?,?,?,?,?,?,?,?,?,? );";
    my $insert = $dbh->prepare( $sql ) || die "Cannot prepare $db: $DBI::errstr";
    $dbh->do("BEGIN TRANSACTION;");                                                    
    for my $i ( 0 .. $#pkg_array ) {
        $insert->execute( @{ $pkg_array[$i] } ) || die "Cannot execute prepared queries: $DBI::errstr";
    }
    $dbh->do("COMMIT;");
        
} # }}}

sub queryExpac
# {{{
{
    my @query = @_;
#    my $option = " -S";
#    my @parameter = ( "\"@format\"", $option);
    my @array;
    my $index = 1;
    open(EXPAC, "expac @query |");
    while (<EXPAC>) 
    {
         @array = map { chomp; [split /²/]; } <EXPAC>;
    }
    close(EXPAC);
    
    return @array;
}
# }}}




# # #
# INIT #
# # #

newDatabase();



                                     



## import full expac Sync into database
##echo "begin; $(expac "insert into data (dependson,dependsonp,packagegroup,license,requiredby,optionaldeps,optionaldepsp,builddate,description,downloadsize,installsize,name,packager,projecturl,version) values ('%n','%v','%L','%G','%d','%k','%m','%b','%D','%E','%O','%o','%N','%p','%u');" -S) 
##end;" | sqlite3 $database   
##echo "begin; $(expac "INSERT INTO $packages (name, version, license, packageGroup, description, downloadSize, installSize,  buildDate, dependsOn, dependsOnP, optionalDeps, optionalDepsP, requiredBy, packager, projectUrl) VALUES ('%n','%v','%L','%G','%d','%k','%m','%b','%D','%E','%O','%o','%N','%p','%u');" -S) 
##end;" > expacSync.raw 
#
#cat expacSync.raw|sqlite3 $database 
#
